package controllers;

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import lib.UtilsInput;
import models.Reporte2;
import com.google.gson.Gson;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;

import play.db.DB;
import play.mvc.Controller;

public class Reporte_2 extends Controller {
	
	public static void Reporte_2() {
		if(Security.isAuthenticated())
			render();
		else
			Application.login();
    }
	
	public static void getReporte2(String from,String to){
		String query="select COUNT(*) AS CANT , SOL_SERVICIO from SIGPC_SOLICITUD GROUP BY SOL_SERVICIO";
		String str = null;
		Gson gson = new Gson();
    	Connection conn = DB.getConnection();
    	
    	//Lista de objetos 
    	List<Reporte2> listReporte2 = new ArrayList<Reporte2>();
    	System.out.println(from);
    	try {
    		if (from != null &&  to != null)
    		{
    			if (!from.equalsIgnoreCase("") &&  !to.equalsIgnoreCase("") )
    			   query = "select COUNT(*) AS CANT , SOL_SERVICIO from SIGPC_SOLICITUD WHERE SOL_FECHA BETWEEN '" + from + "' AND '" + to + "' GROUP BY SOL_SERVICIO ";
    		
    		}
      		
        	ResultSet rs = conn.createStatement().executeQuery(query);
        	
        	 while (rs.next()) {
        		 Reporte2 reporte2 = new Reporte2(rs.getString("SOL_SERVICIO"),rs.getInt("CANT"));
        		 listReporte2.add(reporte2);
        		 
        	        	
        	 }
        	 if(listReporte2.size()!=0){
	        	 str = "{\"success\":true ,\"records\":" + listReporte2.size()
	     				+ ", \"total\":1 , \"page\":1, \"rows\":" + gson.toJson(listReporte2)
	     				+ "}";
	        	 
	      		renderJSON(str);
        	 }
    		 else{
    			 str = "{\"success\":false,\"records\":" + listReporte2.size()
    						+ ", \"total\":0 , \"page\":0}";
    				
    				 renderJSON(str);
    		 }

     		
        	        	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//JASPER REPORT
		public static void  download_report(String from, String to, String formato) {
		    Map reportParams = new HashMap();
		    
		   if (formato != null)
		   {
		  reportParams.put("from", from);
		  reportParams.put("to",  to);
		    
		  if (formato.equalsIgnoreCase("html"))
			renderHtml( UtilsInput.toString((ByteArrayInputStream) lib.BaseJasperReport.generateReport("reporte_2", reportParams,formato)));
			
		  else
			  renderBinary(lib.BaseJasperReport.generateReport("reporte_2", reportParams,formato), "reporte_2."+ formato);
		   
		   }
		  }
	

}
